package com.common.dao.jdbc.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.annotation.Resource;

import org.apache.log4j.Logger;
import org.springframework.context.annotation.Scope;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcCall;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import com.common.dao.jdbc.IBaseJdbcDao;
import com.common.model.Pagination;

@Component("baseJdbcDao")
@Scope("singleton")
@Transactional(readOnly = false)
public class BaseJdbcDao implements IBaseJdbcDao {

	private final static Logger logger = Logger.getLogger("BaseJdbcDao");

	@Resource
	private JdbcTemplate jdbcTemplate = null;

	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;

	}

	@Resource
	private SimpleJdbcTemplate simpleJdbcTemplate = null;

	public SimpleJdbcTemplate getSimpleJdbcTemplate() {
		return simpleJdbcTemplate;
	}

	public void setSimpleJdbcTemplate(SimpleJdbcTemplate simpleJdbcTemplate) {
		this.simpleJdbcTemplate = simpleJdbcTemplate;
	}

	@Resource
	private NamedParameterJdbcTemplate namedParameterJdbcTemplate = null;

	public NamedParameterJdbcTemplate getNamedParameterJdbcTemplate() {
		return namedParameterJdbcTemplate;
	}

	public void setNamedParameterJdbcTemplate(
			NamedParameterJdbcTemplate namedParameterJdbcTemplate) {
		this.namedParameterJdbcTemplate = namedParameterJdbcTemplate;
	}

	@Resource
	private SimpleJdbcCall simpleJdbcCall = null;

	public SimpleJdbcCall getSimpleJdbcCall() {
		return simpleJdbcCall;
	}

	public void setSimpleJdbcCall(SimpleJdbcCall simpleJdbcCall) {

		this.simpleJdbcCall = simpleJdbcCall;
	}

	private SimpleJdbcInsert simpleJdbcInsert = null;

	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackForClassName = { "SQLExcetpion" })
	public int executeByTransaction(String[] sqls, Object[] outObjs) {
		int res = 0;
		if (sqls == null) {
			logger.error("sqls is null ");
			return res;
		}
		Object[] reObjs = new Object[sqls.length];
		for (int i = 0; i < sqls.length; i++) {
			String sql = sqls[i];
			if (sql == null) {
				reObjs[i] = null;
				logger.warn("sqls[" + i + "] is null");
			} else {
				sql = sql.trim().toLowerCase();
			}
			if (sql.startsWith("select")) {
				reObjs[i] = jdbcTemplate.queryForList(sql);
			} else {
				reObjs[i] = jdbcTemplate.update(sql);
			}
		}
		if (outObjs != null) {
			int reLen = reObjs.length;
			int outLen = outObjs.length;
			int len = reLen <= outLen ? reLen : outLen;
			System.arraycopy(reObjs, 0, outObjs, 0, len);
		}
		res = sqls.length;
		return res;
	}

	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackForClassName = { "SQLExcetpion" })
	public int executeByTransaction(String[] sqls,
			List<Map<String, Object>> inParms, Object[] outObjs) {
		int res = 0;
		if (sqls == null) {
			logger.error("sqls is null ");
			return res;
		}
		Object[] reObjs = new Object[sqls.length];
		for (int i = 0; i < sqls.length; i++) {
			String sql = sqls[i];
			if (sql == null) {
				reObjs[i] = null;
				logger.warn("sqls[" + i + "] is null");
			} else {
				sql = sql.trim().toLowerCase();
			}
			Map<String, Object> map = inParms.get(i);
			if (sql.startsWith("select")) {
				reObjs[i] = namedParameterJdbcTemplate.queryForList(sql, map);
			} else {
				reObjs[i] = namedParameterJdbcTemplate.update(sql, map);
			}
		}
		if (outObjs != null) {
			int reLen = reObjs.length;
			int outLen = outObjs.length;
			int len = reLen <= outLen ? reLen : outLen;
			System.arraycopy(reObjs, 0, outObjs, 0, len);
		}
		res = sqls.length;
		return res;
	}

	public int executeForSimple(String sql) {
		return jdbcTemplate.update(sql);
	}

	public int updateForParam(String sql, Map<String, Object> params) {
		return namedParameterJdbcTemplate.update(sql, params);
	}

	public int updateForSimple(String sql) {
		return jdbcTemplate.update(sql);
	}

	public int insertForParam(String sql, Map<String, Object> params) {
		return namedParameterJdbcTemplate.update(sql, params);
	}

	/*
	 * 执行简单的insert,update or delete 语句 @param String sql -- insert, update or
	 * delete 语句
	 * 
	 * @see com.common.dao.jdbc.IBaseJdbcDao#insertForSimple(java.lang.String)
	 */
	public int insertForSimple(String sql) {
		/*
		 * JdbcTemplate是JDBC核心包的中心类，update(String sql)是用来 执行简单的 insert, update
		 * or delete 语句 参数 ：String sql -- insert, update or delete 语句
		 */
		return jdbcTemplate.update(sql);
	}

	public int deleteForParam(String sql, Map<String, Object> params) {
		return namedParameterJdbcTemplate.update(sql, params);
	}

	public int deleteForSimple(String sql) {
		return jdbcTemplate.update(sql);
	}

	@SuppressWarnings("unchecked")
	@Transactional(readOnly = true)
	public List<Map<String, Object>> selectForSimple(String sql) {
		return jdbcTemplate.queryForList(sql);
	}

	@SuppressWarnings("unchecked")
	@Transactional(readOnly = true)
	public List<Map<String, Object>> selectForBean(String sql,
			Map<String, Object> params) {
		return namedParameterJdbcTemplate.queryForList(sql, params);
	}

	@SuppressWarnings("unchecked")
	@Transactional(readOnly = true)
	public List<Map<String, Object>> selectForBean(String sql) {
		return jdbcTemplate.queryForList(sql);
	}

	/*
	 * 
	 * @see com.common.dao.jdbc.IBaseJdbcDao#selectByPaginationWithMySql(java.lang.String,
	 *      java.util.Map, int, int)
	 */
	@SuppressWarnings("unchecked")
	@Transactional(readOnly = true)
	public Pagination selectByPaginationWithMySql(String sql,
			Map<String, Object> paramMap, int pageSize, int page) {
		int selectPosition = sql.toUpperCase().indexOf("SELECT");
		System.out.println("selectPositon :" + selectPosition);
		if (selectPosition < 0) {
			logger.error("invalid select command: " + sql);
			return null;
		}

		if (pageSize <= 0) {
			logger.error("pageSize is <=0 " + pageSize);
			return null;
		}
		String countSql = "SELECT COUNT(*) FROM (" + sql + ") T";
		int rsCount = namedParameterJdbcTemplate
				.queryForInt(countSql, paramMap);
		int pageCount = 0;
		try {
			pageCount = (int) Math.ceil((rsCount + pageSize - 1) / pageSize);
		} catch (Exception e) {
			logger.error("pageCount is error \r\n", e);
			return null;
		}
		int pages = page;
		if (pages > pageCount) {
			pages = pageCount;
		}
		if (pages < 1) {
			pages = 1;
		}
		int spage = (pages - 1) * pageSize;
		StringBuffer sb = new StringBuffer(sql);
		sb.append(" limit ");
		sb.append(spage);
		sb.append(",");
		sb.append(pageSize);
		List<Map<String, Object>> list = namedParameterJdbcTemplate
				.queryForList(sb.toString(), paramMap);
		Pagination pagination = new Pagination();
		pagination.setList(list);
		pagination.setPage(pages);
		pagination.setPageSize(pageSize);
		pagination.setPageCount(pageCount);
		pagination.setRsCount(rsCount);
		return pagination;
	}

	@SuppressWarnings("unchecked")
	@Transactional(readOnly = true)
	public Pagination selectByPaginationWithOracle(String sql,
			Map<String, Object> paramMap, int pageSize, int page) {

		int selectPosition = sql.toUpperCase().indexOf("SELECT");
		if (selectPosition < 0) {
			logger.error("invalid select command: " + sql);
			return null;
		}

		if (pageSize <= 0) {
			logger.error("pageSize is <=0 " + pageSize);
			return null;
		}
		String countSql = "SELECT COUNT(*) FROM (" + sql + ") ___T";
		int rsCount = namedParameterJdbcTemplate
				.queryForInt(countSql, paramMap);
		int pageCount = 0;
		try {
			pageCount = (int) Math.ceil((rsCount + pageSize - 1) / pageSize);
		} catch (Exception e) {
			logger.error("pageCount is error \r\n", e);
			return null;
		}
		int pages = page;
		if (pages > pageCount) {
			pages = pageCount;
		}
		if (pages < 1) {
			pages = 1;
		}
		int spage = (pages - 1) * pageSize;
		// generate sql for oracle pagination
		StringBuffer sb = new StringBuffer("SELECT * FROM (");
		sb.append(sql.substring(0, selectPosition + 6));
		sb.append(" rownum as ROW_NUM, ");
		sb.append(sql.substring(selectPosition + 6));
		sb.append(" ) WHERE ROW_NUM BETWEEN " + spage + " AND "
				+ (spage + pageSize));

		logger.debug("execute pagination sql: " + sb.toString());

		List<Map<String, Object>> list = namedParameterJdbcTemplate
				.queryForList(sb.toString(), paramMap);
		Pagination pagination = new Pagination();
		pagination.setList(list);
		pagination.setPage(pages);
		pagination.setPageSize(pageSize);
		pagination.setPageCount(pageCount);
		pagination.setRsCount(rsCount);
		return pagination;
	}

	public int insertGetGeneratedKey(final String sql,
			final List<Object> params, final String[] keyNames) {
		KeyHolder keyHolder = new GeneratedKeyHolder();
		return jdbcTemplate.update(new PreparedStatementCreator() {
			public PreparedStatement createPreparedStatement(
					Connection connection) throws SQLException {
				PreparedStatement ps = connection.prepareStatement(sql,
						keyNames);
				for (int i = 1; i <= params.size(); i++) {
					ps.setObject(i, params.get(i - 1));
				}
				return ps;
			}
		}, keyHolder);
	}

	public Map<String, Object> callProcedure(String procedureName,
			Map<String, Object> map) {
		simpleJdbcCall.withProcedureName(procedureName);
		SqlParameterSource in = new MapSqlParameterSource().addValues(map);
		return simpleJdbcCall.execute(in);
	}

	public KeyHolder insertGetGeneratedKey(String tableName, String key,
			Map<String, Object> param) {
		simpleJdbcInsert = new SimpleJdbcInsert(jdbcTemplate);
		simpleJdbcInsert.withTableName(tableName).usingGeneratedKeyColumns(key);
		return simpleJdbcInsert.executeAndReturnKeyHolder(param);
	}

	public int insertByMap(String tableName, Map<String, Object> fields) {
		simpleJdbcInsert = new SimpleJdbcInsert(jdbcTemplate);
		simpleJdbcInsert.withTableName(tableName);
		return simpleJdbcInsert.execute(fields);
	}

	public int insertByBean(String tableName, Object javaBean) {
		simpleJdbcInsert = new SimpleJdbcInsert(jdbcTemplate);
		simpleJdbcInsert.withTableName(tableName);
		SqlParameterSource parameters = new BeanPropertySqlParameterSource(
				javaBean);
		return simpleJdbcInsert.execute(parameters);
	}

	public KeyHolder insertByBean(String tableName, String key, Object javaBean) {
		simpleJdbcInsert = new SimpleJdbcInsert(jdbcTemplate);
		simpleJdbcInsert.withTableName(tableName).usingGeneratedKeyColumns(key);
		SqlParameterSource parameters = new BeanPropertySqlParameterSource(
				javaBean);
		return simpleJdbcInsert.executeAndReturnKeyHolder(parameters);
	}

	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackForClassName = { "SQLExcetpion" })
	public int[] executeByBatchWithTransaction(String sql, List<Object> list) {
		if (list == null) {
			logger.error("list is null ");
			return null;
		}
		SqlParameterSource[] batch = SqlParameterSourceUtils.createBatch(list
				.toArray());
		return simpleJdbcTemplate.batchUpdate(sql, batch);
	}

	public int[] executeByBatch(String sql, Map<String, Object>[] params) {
		if (sql == null) {
			logger.error("sql is null");
			return null;
		}
		if (params == null) {
			logger.error("params is null");
			return null;
		}
		SqlParameterSource[] batch = SqlParameterSourceUtils
				.createBatch(params);
		return simpleJdbcTemplate.batchUpdate(sql, batch);
	}

	public int[] executeByBatch(String sql, List<Object> list) {
		if (list == null) {
			logger.error("list is null ");
			return null;
		}
		SqlParameterSource[] batch = SqlParameterSourceUtils.createBatch(list
				.toArray());
		return simpleJdbcTemplate.batchUpdate(sql, batch);
	}

	@SuppressWarnings("unchecked")
	public <T> List<T> queryForList(String sql, Map<String, Object> paramMap,
			ParameterizedRowMapper<T> rowMapper) {
		return (List<T>) getNamedParameterJdbcTemplate().query(sql, paramMap,
				rowMapper);
	}

	@SuppressWarnings("unchecked")
	@Transactional(readOnly = true)
	public List<Map<String, Object>> selectForParam(String sql,
			Map<String, Object> paramMap) {
		return namedParameterJdbcTemplate.queryForList(sql, paramMap);
	}

	public int insertBySomeFields(String tableName,
			Map<String, Object> parameters) {
		if (parameters == null) {
			logger.error("parameters is null ");
			return 0;
		}
		Set<String> set = parameters.keySet();
		simpleJdbcInsert = new SimpleJdbcInsert(jdbcTemplate);
		simpleJdbcInsert.withTableName(tableName).usingColumns(
				set.toArray(new String[set.size()]));
		return simpleJdbcInsert.execute(parameters);
	}

	public KeyHolder insertBySomeFields(String tableName, String key,
			Map<String, Object> parameters) {
		if (parameters == null) {
			logger.error("parameters is null ");
			return null;
		}
		Set<String> set = parameters.keySet();
		simpleJdbcInsert = new SimpleJdbcInsert(jdbcTemplate);
		simpleJdbcInsert.withTableName(tableName).usingColumns(
				set.toArray(new String[set.size()])).usingGeneratedKeyColumns(
				key);
		return simpleJdbcInsert.executeAndReturnKeyHolder(parameters);
	}
}
